﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;
using System.Runtime.InteropServices;
using System.Threading;

namespace BLL
{
    public class PhanLopBLL
    {
        QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
        static int id = 0;
        public IQueryable DanhSachGV(string manamhoc)
        {
            return DB.GiaoViens.Join(
                DB.Tos,
                a => a.MaTo,
                b => b.MaTo,
                (a, b) => new
                {
                    a.MaCanBo,
                    b.TenTo
                }
                ).Join(
                DB.CanBos,
                c => c.MaCanBo,
                d => d.MaCanBo,
                (c, d) => new
                {
                    d.MaCanBo,
                    d.HoTen,
                    c.TenTo
                }
                ).Where(s => !(from p in DB.PhanLops where p.MaNamHoc == manamhoc select p.MaCanBo).Contains(s.MaCanBo)).AsQueryable();
        }
        public IQueryable getDanhSachPhanLop(string manamhoc)
        {
            return DB.PhanLops.Join(
                DB.NienKhoas,
                a => a.MaNienKhoa,
                b => b.MaNienKhoa,
                (a, b) => new
                {
                    a.MaPhanLop,
                    a.MaNamHoc,
                    a.MaLop,
                    a.MaCanBo,
                    b.TenNienKhoa
                }
                ).Join(
                DB.NamHocs,
                a => a.MaNamHoc,
                b => b.MaNamHoc,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaPhanLop,
                    b.TenNamHoc,
                    a.MaLop,
                    a.MaCanBo,
                    a.TenNienKhoa
                }
                ).Join(
                DB.Lops,
                a => a.MaLop,
                b => b.MaLop,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaPhanLop,
                    a.TenNamHoc,
                    b.TenLop,
                    a.MaCanBo,
                    a.TenNienKhoa
                }
                ).Join(
                DB.CanBos,
                a => a.MaCanBo,
                b => b.MaCanBo,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaPhanLop,
                    a.TenNamHoc,
                    a.TenLop,
                    a.MaCanBo,
                    b.HoTen,
                    a.TenNienKhoa
                }
                ).Where(s=>s.MaNamHoc==manamhoc).AsQueryable();
        }
        public IQueryable getDanhSachPhanLopTheoNamHoc(string manamhoc)
        {
            return DB.PhanLops.Join(
                DB.NamHocs,
                a => a.MaNamHoc,
                b => b.MaNamHoc,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaNienKhoa,
                    a.MaPhanLop,
                    b.TenNamHoc,
                    a.MaLop,
                    a.MaCanBo,
                }
                ).Join(
                DB.Lops,
                a => a.MaLop,
                b => b.MaLop,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaNienKhoa,
                    a.MaPhanLop,
                    a.TenNamHoc,
                    b.TenLop,
                    a.MaCanBo,
                }
                ).Join(
                DB.CanBos,
                a => a.MaCanBo,
                b => b.MaCanBo,
                (a, b) => new
                {
                    a.MaNamHoc,
                    a.MaNienKhoa,
                    a.MaPhanLop,
                    a.TenNamHoc,
                    a.TenLop,
                    a.MaCanBo,
                    b.HoTen,
                }
                ).Where(s=>s.MaNamHoc==manamhoc).AsQueryable();
        }
        public string Insert(PhanLopDTO phanlop)
        {
            try
            {
                PhanLop pl = new PhanLop();
                pl.MaPhanLop = (id++).ToString();
                pl.MaLop = phanlop.MaLop;
                pl.MaNamHoc = phanlop.MaNamHoc;
                pl.MaNienKhoa = phanlop.MaNienKhoa;
                pl.MaCanBo = phanlop.MaCanBo;

                DB.PhanLops.InsertOnSubmit(pl);
                DB.SubmitChanges();
                return "t";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public bool Update(PhanLopDTO phanlop)
        {
            try
            {
                PhanLop pl = DB.PhanLops.Where(s => s.MaPhanLop == phanlop.MaPhanLop).FirstOrDefault();
                pl.MaLop = phanlop.MaLop;
                pl.MaNamHoc = phanlop.MaNamHoc;
                pl.MaNienKhoa = phanlop.MaNienKhoa;
                pl.MaCanBo = phanlop.MaCanBo;
                DB.SubmitChanges();
                return true;

            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public bool Delete(string maphanlop)
        {
            try
            {
                PhanLop pl = DB.PhanLops.Where(s => s.MaPhanLop == maphanlop).FirstOrDefault();
                DB.PhanLops.DeleteOnSubmit(pl);
                DB.SubmitChanges();
                return true;

            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public List<LopDTO> getLop(string makhoi,string namhoc)
        {
            //lay het du lieu tu bang khoi
            List<Lop> list = DB.Lops.Where(s => s.MaKhoi == makhoi && !(from p in DB.PhanLops where p.MaNamHoc==namhoc select p.MaLop).Contains(s.MaLop)).ToList<Lop>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<LopDTO> listdto = new List<LopDTO>();
            foreach (Lop lop in list)
            {
                LopDTO k = new LopDTO();
                k.Malop = lop.MaLop;
                k.Tenlop = lop.TenLop;
                listdto.Add(k);
            }
            return listdto;
        }
        public List<LopDTO> getLopAll()
        {
            //lay het du lieu tu bang khoi
            List<Lop> list = DB.Lops.ToList<Lop>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<LopDTO> listdto = new List<LopDTO>();
            foreach (Lop lop in list)
            {
                LopDTO k = new LopDTO();
                k.Malop = lop.MaLop;
                k.Tenlop = lop.TenLop;
                listdto.Add(k);
            }
            return listdto;
        }
        public List<NienKhoaDTO> getNienKhoaChuaKetThuc()
        {
            //lay het du lieu tu bang khoi
            List<NienKhoa> list = DB.NienKhoas.Where(s => s.TrangThai=="Chưa kết thúc").ToList<NienKhoa>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<NienKhoaDTO> listdto = new List<NienKhoaDTO>();
            foreach (NienKhoa lop in list)
            {
                NienKhoaDTO k = new NienKhoaDTO();
                k.Manienkhoa = lop.MaNienKhoa;
                k.TenNienKhoa = lop.TenNienKhoa;
                listdto.Add(k);
            }
            return listdto;
        }
        public List<NamHocDTO> getNamHocALL()
        {
            //lay het du lieu tu bang khoi
            List<NamHoc> list = DB.NamHocs.ToList<NamHoc>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<NamHocDTO> listdto = new List<NamHocDTO>();
            foreach (NamHoc lop in list)
            {
                NamHocDTO k = new NamHocDTO();
                k.MaNamHoc = lop.MaNamHoc;
                k.TenNamHoc = lop.TenNamHoc;
                listdto.Add(k);
            }
            return listdto;
        }
        public bool kiemtraNamHocKetThuc(string manamhoc)
        {
            string tt = DB.NamHocs.Select(s => s.TrangThai).FirstOrDefault().ToString();
            if (tt.Equals("Chưa kết thúc"))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public IQueryable DanhSachGV_Hoan(string tengiaovien)
        {
            return DB.GiaoViens.Join(
                DB.Tos,
                a => a.MaTo,
                b => b.MaTo,
                (a, b) => new
                {
                    a.MaCanBo,
                    b.TenTo
                }
                ).Join(
                DB.CanBos,
                c => c.MaCanBo,
                d => d.MaCanBo,
                (c, d) => new
                {
                    d.MaCanBo,
                    d.HoTen,
                    c.TenTo
                }
                ).Where(s=>s.HoTen.Contains(tengiaovien)).AsQueryable();
        }
    }
}
